Data Handling and Visualization


MGMT 675
AI-Assisted Financial Analysis
Kerry Back

Data Handling

  • Merge
  • Filter
  • Sort
  • Aggregate by group
  • Transform

Visualization

  • Distributions
    • Histograms, density plots, box plots, pie charts
  • Bivariate
    • Line plots, scatter plots, scatter plots with regression lines
  • 3D
    • 3D plots, contour plots
  • Interactive plots saved as html

Datasets

  • metrics.xlsx and tickers.xlsx.
  • Online data from various sources.

Merge metrics and tickers

  • Ask Julius to merge the datasets on the ticker column
  • Ask Julius the following.
    • How many rows are there?
    • What are the column names?
    • What are the unique values in the category column?
    • What are the unique values in the sector column?
    • Show the head of the data frame.

Filter

  • Ask Julius to filter on the category column to “Domestic Common Stock” and “Domestic Common Stock Primary Class.” Ask Julius to call this data frame common_stock.
  • Ask Julius to create a copy of the common_stock data frame that contains only rows for which pe>0.
  • Ask Julius to create a copy of the common_stock data frame that contains only rows for which marketcap is above the median marketcap.

Sort

  • Ask Julius to sort on marketcap in descending order and to show the head of the data frame.

Aggregate by group

  • Ask Julius to describe marketcap.
  • Ask Julius to compute the mean marketcap by sector.
  • Ask Julius to compute the number of firms by sector.
  • Ask Julius to compute the total marketcap by sector.

  • Ask Julius to compute the mean pe grouped by (sector, scalemarketcap) and to display the results as a two-dimensional table.
  • Ask Julius to recreate the table using only rows for which pe > 0.
  • Ask Julius to compute the percent of firms for which pe < 0 by sector.

Transform

  • Ask Julius to create a new variable equal to the rank of marketcap in descending order.
  • Ask Julius to create a new variable that is 1 if pe>0 and 0 otherwise (a dummy variable).
  • Ask Julius to create a new variable equal to the excess of pb over the median sector pb.

Yahoo Finance

  • Daily open, high, low, close, adjusted close, volume
  • Income statement, balance sheet, and statement of cash flows for past 5 years
  • Current market option data (bid, ask, last price, open interest, implied volatility, …)

Federal Reserve Economic Data

  • Ask Julius to use the pandas-datareader to get the history of crude oil prices from FRED.
  • Ask Julius to get the history of 3-month, 1-year, 5-year, and 10-year Treasury yields from FRED.
  • Ask Julius to get the history of inflation rates from FRED.

Ken French’s Data Library

  • Ask Julius to get the Fama-French factors from Ken French’s data library.
  • Ask Julius to list the datasets on Ken French’s data library.
  • Ask Julius to get the 48 industry returns from Ken French’s data library.

Other Data

  • Ask Julius to find the constituents of the S&P 100.
  • When Julius provides a link, ask Julius to read the table at the link.

Visualization Examples

  • Ask Julius to create a density plot of marketcap in the metrics dataset.
  • Ask Julius to save as a jpeg and provide a link.

  • Ask Julius to create a bar chart of total marketcap by sector using the merged dataset.
  • Ask Julius to make the font size larger.
  • Ask Julius to create a pie chart of the number of firms by sector using the merged dataset.

  • Ask Julius to use seaborn to create a boxplot of marketcap by sector. Ask Julius to save as a png.
  • Ask Julius to use plotly to create a boxplot of marketcap by sector in the merged dataset and to include the ticker in the hover data. Ask Julius to save as html.

  • Ask Julius to calculate the daily percent changes in the crude oil price and to create a filled density plot using seaborn.
  • Ask Julius to create a filled contour plot of the function \(z=(4x + y)^2\) for \(x\) and \(y\) between \(-2\) and \(2\).
  • Ask Julius to create a 3D plot of the same function on the same range.

  • Ask Julius to use plotly to create a figure containing line plots of the 3-month, 1-year, 5-year, and 10-year Treasury yields. Ask Julius to save as html.
  • Ask Julius to recreate the figure using the plotly_white template.
  • Ask Julius to use plotly to create an animation showing a plot of the 3-month, 1-year, 5-year, and 10-year Treasury yields as a function of maturity. Tell Julius to use the date as the animation frame.